package com.reminis.exceldemo.web;

import com.reminis.exceldemo.entity.Yeji;
import com.reminis.exceldemo.entity.Yeji_download;
import com.reminis.exceldemo.service.YejiService;
import com.reminis.exceldemo.util.ExcelUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.util.StringUtils;
import org.springframework.util.CollectionUtils;

import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@CrossOrigin("*")
@RestController
@RequestMapping("/api/yeji")
public class ZhouyejiController {

    private static final Logger log = LogManager.getLogger(ZhouyejiController.class);

    @Autowired
    private YejiService YejiService;
    /**
     * 业绩表Excel导出
     *
     */
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletRequest request,HttpServletResponse response){
       // log.info("业绩表 start");
        String like = request.getParameter("like");
       // log.info("打印模糊查询参数："+like);
        // 创建一个dingdanList对象列表，用于存储仓库数据
        List<Yeji_download> yejiList = new ArrayList<>();
        yejiList = YejiService.getLikeSelectDingdan(like);
      log.info("业绩表List"+yejiList.toString());
        //long t1 = System.currentTimeMillis();
        String fileName = "周业绩表";
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String format = simpleDateFormat.format(date);
        fileName=fileName+format;
        //注意，实体类ExcelColumn注解的col为写入Excel的顺序，如注解不写col则不会显示在Excel中
        ExcelUtils.writeExcel(request,response, fileName,yejiList, Yeji_download.class);
       // ExcelUtils.writeExcel(request,response, fromqfList, fromqf.class);
        //long t2 = System.currentTimeMillis();
       // log.info(String.format("write over! cost:%sms", (t2 - t1)));
    }



    /**
     * 处理dingdan仓库数据并返回一个包含特定数据对象的列表
     * 这个方法主要用于模拟从仓库中获取数据并封装到dingdan对象列表中
     */
    public List<Yeji> DingdanHandleRepositoryData() {
        // 创建一个dingdanList对象列表，用于存储仓库数据
        List<Yeji> yejiList = new ArrayList<>();

        yejiList = YejiService.getAll();
        for (int i = 0; i <yejiList.size() ; i++) {
          log.info("打印循环参数yejiList:"+yejiList.get(i).toString());
        }
        // 返回填充好的fromqf对象列表
        return yejiList;
    }


    /**
     * 周业绩表页面分页查询
     * @return
     */
    @GetMapping("/getList")
    public Map<String,List<Yeji>> getList(int page,int limit){
        //获取数据库中的查询数据
        List<Yeji> yejiList = YejiService.getLimitAll(page,limit);
        Map<String,List<Yeji>> map = new HashMap<String, List<Yeji>>();
        map.put("data",yejiList);
        return map;
    }


    /**
     * 周业绩表页面 like 模糊分页查询
     * @return
     */
    @GetMapping("/getLikeList")
    public Map<String,List<Yeji>> getLikeList(String like,int page,int limit){
        //获取数据库中的查询数据
      log.info("打印yejiList模糊查询参数："+like);
        // page-1，因为layui的page从1开始，数据库从0开始,所以page-1用于前端分页

        //page =page-1;
        List<Yeji> yejiList = YejiService.getLikeList(like,page,limit);
      log.info("结束yejiList模糊查询打印："+yejiList.toString());
        Map<String,List<Yeji>> map = new HashMap<String, List<Yeji>>();
        map.put("data",yejiList);
        return map;
    }


    /**
     * 模糊查询总数，用于layui 分页
     * @return
     */
    @RequestMapping("/getLikeCount")
    @ResponseBody
    public String getLikeCount(String like){
      log.info("模糊查询总数参数："+like);
        int count = YejiService.getLikeCount(like);
       // log.info("模糊查询总数结果："+count);
        String data = "{\"count\":"+count+"}";
        return data;
    }
    /**
     * 订单追踪页面查询数据总数，用于layui 分页
     * @return
     */
    @RequestMapping("/getCount")
    @ResponseBody
    public String getCount(){
        int count = YejiService.getCount();
        String data = "{\"count\":"+count+"}";
        return data;
    }


    @RequestMapping("/addZhouyeji")
    @ResponseBody
    public String addZhouyeji(String zhongleiname,String zhonglei,String danwei){
        //判断中类是否已存在
        //判断中类中文是否已存在
        //判断品种
        zhongleiname = zhongleiname.trim();
        String verify = YejiService.verify(zhongleiname.trim(),zhonglei);//中类中文，品种，去除空格校验，防止空格影响查询
        if(verify.equals("验证通过")){
            Yeji yeji = new Yeji();
            yeji.set中类中文(zhongleiname);
            yeji.set中类(zhonglei);
            yeji.set单位(danwei);
            boolean addyeji = YejiService.addyeji(yeji);
            //    log.info("打印添加结果："+add);
            if (addyeji ) {
              log.info("打印添加OK结果："+addyeji);
                return "添加成功";
            }
        }
        //返回添加失败原因
      log.info("打印添加失败原因："+verify);
            return verify;
    }

    @RequestMapping("/Yejidelete")
    @ResponseBody
    public String Yejidelete(String weiyihaoma) {
     //   log.info("打印删除参数：" + weiyihaoma);

            boolean deleted = YejiService.UpdateZhongleiNameDate(weiyihaoma);
     //   log.info("打印s删除结果："+deleted);
        if (deleted) {
            return "ok";
        }
        return "error";
    }

    /**
     * 周业绩编辑界面
     * @return
     */
    @RequestMapping("/editZhouyeji")
    @ResponseBody
    public String Yejidelete(HttpServletRequest request) {
      log.info("打印业绩编辑对象："+request.getParameter("weiyihaoma"));
        Yeji_download yeji = new Yeji_download();
        yeji.set唯一号码(request.getParameter("weiyihaoma"));
        yeji.set中类中文(request.getParameter("zhongleiname"));
        yeji.set中类(request.getParameter("zhonglei"));
        yeji.set单位(request.getParameter("danwei"));

        yeji.set下单数量(Double.parseDouble(request.getParameter("xiadanshuliang")));
        yeji.set下单金额(BigDecimal.valueOf(Double.parseDouble(request.getParameter("xiadanjinge"))));
        yeji.set发货数量(Double.parseDouble(request.getParameter("fahuoshuliang")));
        yeji.set发货金额(BigDecimal.valueOf(Double.parseDouble(request.getParameter("fahuojinge"))));
        boolean flag = YejiService.UpdateZhongleAll(yeji);

        if (flag) {
            return "ok";
        }
        return "error";
    }


    /**
     * Excel导入
     * 2025年3月31日16:17:24 新增周业绩界面，上传下单及发货数量Excel
     * V3: 分Sheet读取，更新指定字段，增加错误处理
     * @param file 上传的Excel文件
     * @return ResponseEntity 包含处理结果的JSON对象
     */
    @PostMapping("/uploadExcel")
    public ResponseEntity<Map<String, Object>> uploadExcel(@RequestBody MultipartFile file) { 
        log.info("开始处理上传的周业绩Excel文件： {}", file.getOriginalFilename());
        Map<String, Object> responseBody = new HashMap<>();

        try {
            // 1. 重置业绩字段
            log.info("重置周业绩表的数量和金额字段...");
            YejiService.updateZhouYeji(); 

            // --- Sheet 1 处理 --- 
            log.info("读取并处理第一个Sheet (下单数据)..." );
            BigDecimal calculatedOrderTotal = BigDecimal.ZERO; // 初始化订单总额累加器
            List<Yeji> orderDataList = ExcelUtils.readOrderDataFromSheet1(file);

            // +++ 新增: 校验Sheet 1 中类是否有忽略大小写的重复值 +++
            if (!CollectionUtils.isEmpty(orderDataList)) {
                List<String> orderZhongleiList = orderDataList.stream()
                                                            .map(Yeji::get中类)
                                                            .filter(StringUtils::hasText) // 过滤掉空的或只有空格的中类
                                                            .collect(Collectors.toList());
                List<String> duplicateOrderZhonglei = ExcelUtils.findCaseInsensitiveDuplicates(orderZhongleiList);
                if (!duplicateOrderZhonglei.isEmpty()) {
                    String allDuplicateOccurrences = String.join(", ", duplicateOrderZhonglei);
                    String errorMsg = String.format("上传失败： (下单数据) 中存在重复的中类: %s", 
                                                         allDuplicateOccurrences);
                    log.warn(errorMsg);
                    responseBody.put("code", 6); // 新错误码: Sheet 1 中类重复
                    responseBody.put("msg", errorMsg);
                    return ResponseEntity.ok(responseBody);
                }
                log.info("Sheet 1 (下单数据) 中类无忽略大小写重复值，校验通过。");
            }
            // +++ 校验结束 +++

            for (Yeji orderItem : orderDataList) {
                if ("总计".equals(orderItem.get中类())) {
                    continue; 
                }
                // 校验中类是否存在
                Yeji existingYeji = YejiService.getByZhongleiAndZhongleiZhongwen(orderItem.get中类());
                if (existingYeji == null) {
                    responseBody.put("code", 1);
                    responseBody.put("msg", "Sheet 1 中的中类 '" + orderItem.get中类() + "' 在周业绩表中不存在");
                    log.warn("上传失败: {}", responseBody.get("msg"));
                    return ResponseEntity.ok(responseBody); 
                }

                // 累加下单金额 (增加解析和校验)
                String orderAmountStr = orderItem.get下单金额();
                BigDecimal orderAmount = BigDecimal.ZERO; // 默认为0，如果需要非空则调整逻辑
                if (StringUtils.hasText(orderAmountStr)) {
                    try {
                        orderAmountStr = orderAmountStr.replace(",", ""); // 处理可能的逗号
                        orderAmount = new BigDecimal(orderAmountStr);
                    } catch (NumberFormatException e) {
                         log.error("Sheet 1 中类 '{}' 的下单金额 '{}' 无法解析为数字.", orderItem.get中类(), orderAmountStr, e);
                         responseBody.put("code", 4); // 新增错误码：数据格式错误
                         responseBody.put("msg", "Sheet 1 中类 '" + orderItem.get中类() + "' 的下单金额格式错误: " + orderAmountStr);
                         return ResponseEntity.ok(responseBody);
                    }
                }
                calculatedOrderTotal = calculatedOrderTotal.add(orderAmount);
                log.trace("累加下单金额: {} -> 当前总计: {}", orderAmount, calculatedOrderTotal);

                // 更新下单数据 (数量和金额)
                YejiService.updateOrderData(orderItem.get中类(), orderItem.get下单数量(), orderItem.get下单金额()); // 金额仍传递原始字符串
                // log.debug("更新下单数据 - 中类: {}, 数量: {}, 金额: {}", orderItem.get中类(), orderItem.get下单数量(), orderItem.get下单金额());
            }

            // **校验 Sheet 1 总金额**
            log.info("第一个Sheet处理完成. 计算得到的下单总额: {}", calculatedOrderTotal);
            try {
                BigDecimal excelOrderTotal = ExcelUtils.readTotalAmountFromSheet(file, 0, 2); // Sheet 0, 金额在第3列(索引2)
                
                // 设置精度为两位小数进行比较
                BigDecimal scaledCalculatedOrderTotal = calculatedOrderTotal.setScale(2, RoundingMode.HALF_UP);
                BigDecimal scaledExcelOrderTotal = excelOrderTotal.setScale(2, RoundingMode.HALF_UP);
                
                if (scaledCalculatedOrderTotal.compareTo(scaledExcelOrderTotal) != 0) {
                     log.warn("Sheet 1 金额校验失败 (保留两位小数): 计算总额 {} != Excel总计行金额 {}", scaledCalculatedOrderTotal, scaledExcelOrderTotal);
                     responseBody.put("code", 2); // 错误码 2: Sheet 1 金额不匹配
                     responseBody.put("msg", "Sheet 1 下单金额总计 (" + scaledCalculatedOrderTotal + ") 与Excel总计行金额 (" + scaledExcelOrderTotal + ") 不符 (保留两位小数)");
                     return ResponseEntity.ok(responseBody);
                } else {
                     log.info("Sheet 1 金额校验成功 (保留两位小数).");
                 }
            } catch (Exception e) {
                 log.error("读取或校验Sheet 1总计金额时出错.", e);
                 responseBody.put("code", 5); // 新增错误码：校验总额时出错
                 responseBody.put("msg", "读取或校验Sheet 1总计金额时出错: " + e.getMessage());
                 return ResponseEntity.ok(responseBody);
            }

            // --- Sheet 2 处理 --- 
            log.info("读取并处理第二个Sheet (发货数据)..." );
            BigDecimal calculatedShipmentTotal = BigDecimal.ZERO; // 初始化发货总额累加器
            List<Yeji> shipmentDataList = ExcelUtils.readShipmentDataFromSheet2(file);

            // +++ 新增: 校验Sheet 2 中类是否有忽略大小写的重复值 +++
            if (!CollectionUtils.isEmpty(shipmentDataList)) {
                List<String> shipmentZhongleiList = shipmentDataList.stream()
                                                                  .map(Yeji::get中类)
                                                                  .filter(StringUtils::hasText)
                                                                  .collect(Collectors.toList());
                List<String> duplicateShipmentZhonglei = ExcelUtils.findCaseInsensitiveDuplicates(shipmentZhongleiList);
                if (!duplicateShipmentZhonglei.isEmpty()) {
                    String allDuplicateOccurrences = String.join(", ", duplicateShipmentZhonglei);
                    String errorMsg = String.format("上传失败,(发货数据) 中存在重复的中类: %s", allDuplicateOccurrences);
                    log.warn(errorMsg);
                    responseBody.put("code", 7); // 新错误码: Sheet 2 中类重复
                    responseBody.put("msg", errorMsg);
                    return ResponseEntity.ok(responseBody);
                }
                log.info("Sheet 2 (发货数据) 中类无忽略大小写重复值，校验通过。");
            }
            // +++ 校验结束 +++

            for (Yeji shipmentItem : shipmentDataList) {
                if ("总计".equals(shipmentItem.get中类())) {
                    continue; 
                }
                // 再次校验中类是否存在
                 Yeji existingYeji = YejiService.getByZhongleiAndZhongleiZhongwen(shipmentItem.get中类());
                 if (existingYeji == null) {
                     responseBody.put("code", 1);
                     responseBody.put("msg", "Sheet 2 中的中类 '" + shipmentItem.get中类() + "' 在周业绩表中不存在");
                     log.warn("上传失败: {}", responseBody.get("msg"));
                     return ResponseEntity.ok(responseBody); 
                 }

                 // 累加发货金额 (增加解析和校验)
                 String shipmentAmountStr = shipmentItem.get发货金额();
                 BigDecimal shipmentAmount = BigDecimal.ZERO;
                 if (StringUtils.hasText(shipmentAmountStr)) {
                     try {
                         shipmentAmountStr = shipmentAmountStr.replace(",", "");
                         shipmentAmount = new BigDecimal(shipmentAmountStr);
                     } catch (NumberFormatException e) {
                         log.error("Sheet 2 中类 '{}' 的发货金额 '{}' 无法解析为数字.", shipmentItem.get中类(), shipmentAmountStr, e);
                         responseBody.put("code", 4); // 错误码 4: 数据格式错误
                         responseBody.put("msg", "Sheet 2 中类 '" + shipmentItem.get中类() + "' 的发货金额格式错误: " + shipmentAmountStr);
                         return ResponseEntity.ok(responseBody);
                     }
                 }
                 calculatedShipmentTotal = calculatedShipmentTotal.add(shipmentAmount);
                 log.trace("累加发货金额: {} -> 当前总计: {}", shipmentAmount, calculatedShipmentTotal);

                 // 更新发货数据 (数量和金额)
                 YejiService.updateShipmentData(shipmentItem.get中类(), shipmentItem.get发货数量(), shipmentItem.get发货金额());
                 // log.debug("更新发货数据 - 中类: {}, 数量: {}, 金额: {}", shipmentItem.get中类(), shipmentItem.get发货数量(), shipmentItem.get发货金额());
            }

            // **校验 Sheet 2 总金额**
            log.info("第二个Sheet处理完成. 计算得到的发货总额: {}", calculatedShipmentTotal);
            try {
                 BigDecimal excelShipmentTotal = ExcelUtils.readTotalAmountFromSheet(file, 1, 2); // Sheet 1, 金额在第3列(索引2)
                 
                 // 设置精度为两位小数进行比较
                 BigDecimal scaledCalculatedShipmentTotal = calculatedShipmentTotal.setScale(2, RoundingMode.HALF_UP);
                 BigDecimal scaledExcelShipmentTotal = excelShipmentTotal.setScale(2, RoundingMode.HALF_UP);
                 
                 if (scaledCalculatedShipmentTotal.compareTo(scaledExcelShipmentTotal) != 0) {
                      log.warn("Sheet 2 金额校验失败 (保留两位小数): 计算总额 {} != Excel总计行金额 {}", scaledCalculatedShipmentTotal, scaledExcelShipmentTotal);
                      responseBody.put("code", 3); // 错误码 3: Sheet 2 金额不匹配
                      responseBody.put("msg", "Sheet 2 发货金额总计 (" + scaledCalculatedShipmentTotal + ") 与Excel总计行金额 (" + scaledExcelShipmentTotal + ") 不符 (保留两位小数)");
                      return ResponseEntity.ok(responseBody);
                 } else {
                      log.info("Sheet 2 金额校验成功 (保留两位小数).");
                  }
            } catch (Exception e) {
                  log.error("读取或校验Sheet 2总计金额时出错.", e);
                  responseBody.put("code", 5); // 错误码 5: 校验总额时出错
                  responseBody.put("msg", "读取或校验Sheet 2总计金额时出错: " + e.getMessage());
                  return ResponseEntity.ok(responseBody);
            }

            // 4. 全部成功，准备成功响应
            log.info("周业绩Excel文件上传处理成功 (包含金额校验): {}", file.getOriginalFilename());
            responseBody.put("code", 0); 
            responseBody.put("msg", "上传成功");
            return ResponseEntity.ok(responseBody);

        } catch (Exception e) {
            // 捕获其他未预料的异常 (如文件读取IO错误、Service层/Mapper层更深层错误等)
            log.error("周业绩Excel文件上传处理时发生意外错误: {}", file.getOriginalFilename(), e);
            responseBody.put("code", 500); 
            responseBody.put("msg", "上传失败：处理文件时发生内部错误，请联系管理员。");
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(responseBody);
        }
    }
}
